package com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.repository.impl;

import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.entity.*;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.param.CasualtiesDECalculationParam;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.repository.CasualtiesDERepository;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.vo.CasualtiesCalculationVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.vo.CasualtiesResultVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.casualtiesDE.vo.ExportVO;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.entity.EarthquakeAffectedField;
import com.css.fxfzfxqh.modules.buildings.definitiveEvaluation.resistEarthquakesDE.param.AssessParam;
import com.css.fxfzfxqh.modules.largeScreen.entity.Calculation;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import com.css.fxfzfxqh.web.PlatformSessionContext;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/06/08
 * @Description: 确定评估_人员伤亡
 */
@Repository
public class CasualtiesDERepositoryImpl implements CasualtiesDERepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    //抗人员伤亡_确定评估_评估记录主表
    private static final String TABLE_NAME = "fxqh_casualties_assess_ed";
    //确定评估_人员伤亡_计算
    private static final String TABLE_CALCULATION = "fxqh_casualties_calculation_ed";
    //确定评估_人员伤亡_计算_临时表
    private static final String TABLE_CALCULATION_TEMP = "fxqh_casualties_calculation_ed_temp";

    @Override
    public void addAssess(CasualtiesDE param) {
        String sql = "insert into " + TABLE_NAME + "(id,assess_code,assess_name,assess_desc,house_data,house_data_flag," +
                "coordinate_range,calculation,create_user,create_time,del_flag,seismic_input_mode,company,popluation_version,radio2,create_user_name,geom,area_range) " +
                "values(:id,:assessCode,:assessName,:assessDesc,:houseData,:houseDataFlag," +
                ":coordinateRange,:calculation,:createUser,:createTime,:delFlag,:seismicInputMode,:company,:popluationVersion,:radio2,:createUserName,ST_GeometryFromText(:geom,4490),:areaRange)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public CasualtiesDE findById(String id) {
        try {
            String sql = "select * from " + TABLE_NAME + " where id = ? ";
            CasualtiesDE casualtiesDE = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(CasualtiesDE.class), id);
            return casualtiesDE;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void upDateAssess(CasualtiesDE param) {
        String sql = "update " + TABLE_NAME +
                " set assess_name = :assessName,assess_desc = :assessDesc," +
                "update_user=:updateUser,update_time=:updateTime where del_flag = '0' and id = '" + param.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    //根据code获取最近一次新增的数据的code
    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select assess_code from " + TABLE_NAME + " where del_flag = '0' and assess_code like '%" + code + "%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Map<String, Object> getPage(int curPage, int pageSize, AssessParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_NAME);
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        //评估记录编号
        if (!PlatformObjectUtils.isEmpty(param.getAssessCode())) {
            whereSql.append(" and assess_code = '" + param.getAssessCode() + "'");
        }
        //评估记录名称
        if (!PlatformObjectUtils.isEmpty(param.getAssessName())) {
            whereSql.append(" and assess_name = '" + param.getAssessName() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CasualtiesDE.class)).size();
        List<CasualtiesDE> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(CasualtiesDE.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //批量新增计算值
    @Override
    public void batchCalculation(List<CasualtiesDECalculation> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,geography_id,condition,casualties,time,calculated_value,correction_value,coordinate_range,create_user,create_time, geom)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:geographyId,:condition,:casualties,:time,:calculatedValue,:correctionValue,:coordinateRange,:createUser,:createTime, ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    //    //根据条件查询计算值
////    @Override
////    public Map<String, Object> queryCalculation(Integer curPage, Integer pageSize, String id, String condition, String casualties, String time) {
////        int firstIndex = (curPage - 1) * pageSize;
////        int lastIndex = pageSize;
////        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
////        String sql = "select * from "+TABLE_CALCULATION+" where assess_id = '"+id+"' and condition ='"+condition+"' and casualties = '"+casualties+"' and time ='"+time+"'";
////        Integer total = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResistEarthquakesCalculationVO.class)).size();
////        List<CasualtiesDECalculation> list = jdbcTemplate.query(new StringBuilder(sql).append(limitSql).toString()
////                , new BeanPropertyRowMapper<>(CasualtiesDECalculation.class));
////        Map<String, Object> map = new HashMap();
////        map.put("total", total);
////        map.put("rows", list);
////        return map;
////    }
    //根据条件查询计算值
    private Map<String,String> getIntensityMap(){
        Map<String,String> map = new HashMap<>();
        map.put("ld1","Ⅵ");
        map.put("ld2","Ⅶ");
        map.put("ld3","Ⅷ");
        map.put("ld4","Ⅸ");
        map.put("ld5","Ⅹ");
        return map;
    }
    @Override
    public Map<String, Object> queryCalculation(Integer curPage, Integer pageSize, String id, String condition, String casualties, String time, String seismicInputMode) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sqlBuilder = new StringBuilder("select id,assess_id,assess_code,province_name,city_name,county_name,geography_id,intensity");
        //重伤
        if ("rysw1".equals(casualties)) {
            sqlBuilder.append(",").append("'重伤' as casualties");
            if ("sjd1".equals(time)) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_injure as calculatedValue");
                sqlBuilder.append(",").append(" day_injure_correction_value as correctionValue");
            } else if ("sjd2".equals(time)) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_injure as calculatedValue");
                sqlBuilder.append(",").append(" night_injure_correction_value as correctionValue");
            }
        } else if ("rysw2".equals(casualties)) {
            //死亡
            sqlBuilder.append(",").append("'死亡' as casualties");
            if ("sjd1".equals(time)) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_death as calculatedValue");
                sqlBuilder.append(",").append(" day_death_correction_value as correctionValue");
            } else if ("sjd2".equals(time)) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_death as calculatedValue");
                sqlBuilder.append(",").append(" night_death_correction_value as correctionValue");
            }
        }
        sqlBuilder.append(",").append("CASE condition" +
                "        WHEN 'sgglsz2' THEN '50年内超越概率10%' " +
                "        WHEN 'sgglsz3' THEN '50年内超越概率2%'" +
                "        WHEN 'sgglsz4' THEN '100年内超越概率1%'" +
                "        ELSE '50年内超越概率63%'" +
                "    END as condition ");
        StringBuilder whereSql = new StringBuilder(" from fxqh_casualties_calculation_ed where del_flag='0' ");
        if (StringUtils.isNotBlank(id)) {
            whereSql.append(" and assess_id = '").append(id).append("' ");
        }
        if ("0".equals(seismicInputMode)) {
            //烈度 condition就是烈度 ld1-ld5  casualties
            if (StringUtils.isNotBlank(condition)) {
                if ("ld1".equals(condition)) {
                    whereSql.append(" and intensity='Ⅵ'");
                } else if ("ld2".equals(condition)) {
                    whereSql.append(" and intensity='Ⅶ'");
                } else if ("ld3".equals(condition)) {
                    whereSql.append(" and intensity='Ⅷ'");
                } else if ("ld4".equals(condition)) {
                    whereSql.append(" and intensity='Ⅸ'");
                } else if ("ld5".equals(condition)) {
                    whereSql.append(" and intensity='Ⅹ'");
                }
            }
        } else if ("1".equals(seismicInputMode)) {
            //四个概率水准  condition就是四个概率水准版本
            whereSql.append(" and condition='").append(condition).append("'");
        } else if ("2".equals(seismicInputMode)) {
            if (StringUtils.isNotBlank(condition)) {
                //地震影响场 condition就是地震影响场版本
                whereSql.append(" and version='").append(condition).append("'");
            }
        }

        whereSql.append(" and flag='").append(seismicInputMode).append("'");
        //whereSql.append(" and geography_id='32ed67423d1c4985850c60e2f7169ba6' ");
        sqlBuilder.append(whereSql);
        Integer total = jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(CasualtiesCalculationVO.class)).size();
        List<CasualtiesCalculationVO> list = jdbcTemplate.query(sqlBuilder.append(" order by geography_id,intensity asc ").append(limitSql).toString(), new BeanPropertyRowMapper<>(CasualtiesCalculationVO.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    //修改经验评估值
    @Override
    public void updateCorrectionValue(String id, BigDecimal correctionValue, String time, String casualties) {
        StringBuilder sqlBuilder = new StringBuilder("update " + TABLE_CALCULATION + " set ");
        if ("白天".equals(time)) {
            if ("重伤".equals(casualties)) {
                sqlBuilder.append(" day_injure_correction_value =").append(correctionValue);
            }
            if ("死亡".equals(casualties)) {
                sqlBuilder.append(" day_death_correction_value =").append(correctionValue);
            }
        } else if ("夜间".equals(time)) {
            if ("重伤".equals(casualties)) {
                sqlBuilder.append(" night_injure_correction_value =").append(correctionValue);
            }
            if ("死亡".equals(casualties)) {
                sqlBuilder.append(" night_death_correction_value =").append(correctionValue);
            }
        }
        sqlBuilder.append(",update_user ='").append(PlatformSessionContext.getUserID()).append("' ,update_time = now() where id ='").append(id).append("'");
        jdbcTemplate.update(sqlBuilder.toString());
    }

    //根据条件获取省市县的数据
    @Override
    public List<ExportVO> getListPCC(CasualtiesDECalculationParam param) {
        StringBuilder sqlBuilder = new StringBuilder("select id,assess_id,assess_code,province_name,city_name,county_name,geography_id,intensity");
        //重伤
        if ("rysw1".equals(param.getCasualties())) {
            sqlBuilder.append(",").append("'重伤' as casualties");
            if ("sjd1".equals(param.getTime())) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_injure as calculatedValue");
                sqlBuilder.append(",").append(" day_injure_correction_value as correctionValue");
            } else if ("sjd2".equals(param.getTime())) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_injure as calculatedValue");
                sqlBuilder.append(",").append(" night_injure_correction_value as correctionValue");
            }
        } else if ("rysw2".equals(param.getCasualties())) {
            //死亡
            sqlBuilder.append(",").append("'死亡' as casualties");
            if ("sjd1".equals(param.getTime())) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_death as calculatedValue");
                sqlBuilder.append(",").append(" day_death_correction_value as correctionValue");
            } else if ("sjd2".equals(param.getTime())) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_death as calculatedValue");
                sqlBuilder.append(",").append(" night_death_correction_value as correctionValue");
            }
        }
        sqlBuilder.append(",").append("CASE condition" +
                "        WHEN 'sgglsz2' THEN '50年内超越概率10%' " +
                "        WHEN 'sgglsz3' THEN '50年内超越概率2%'" +
                "        WHEN 'sgglsz4' THEN '100年内超越概率1%'" +
                "        ELSE '50年内超越概率63%'" +
                "    END as condition ");
        StringBuilder whereSql = new StringBuilder(" from fxqh_casualties_calculation_ed where del_flag='0' ");
        if (StringUtils.isNotBlank(param.getId())) {
            whereSql.append(" and assess_id = '").append(param.getId()).append("' ");
        }
        if ("0".equals(param.getSeismicInputMode())) {
            //烈度 condition就是烈度 ld1-ld5  casualties
            if (StringUtils.isNotBlank(param.getCondition())) {
                if ("ld1".equals(param.getCondition())) {
                    whereSql.append(" and intensity='Ⅵ'");
                } else if ("ld2".equals(param.getCondition())) {
                    whereSql.append(" and intensity='Ⅶ'");
                } else if ("ld3".equals(param.getCondition())) {
                    whereSql.append(" and intensity='Ⅷ'");
                } else if ("ld4".equals(param.getCondition())) {
                    whereSql.append(" and intensity='Ⅸ'");
                } else if ("ld5".equals(param.getCondition())) {
                    whereSql.append(" and intensity='Ⅹ'");
                }
            }
        } else if ("1".equals(param.getSeismicInputMode())) {
            //四个概率水准  condition就是四个概率水准版本
            whereSql.append(" and condition='").append(param.getCondition()).append("'");
        } else if ("2".equals(param.getSeismicInputMode())) {
            if (StringUtils.isNotBlank(param.getCondition())) {
                //地震影响场 condition就是地震影响场版本
                whereSql.append(" and version='").append(param.getCondition()).append("'");
            }
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            whereSql.append(" and province_name = '" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            whereSql.append(" and city_name = '" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            whereSql.append(" and county_name = '" + param.getCounty() + "'");
        }
        whereSql.append(" and flag='").append(param.getSeismicInputMode()).append("'");
        sqlBuilder.append(whereSql);
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(ExportVO.class));
    }

    @Override
    public List<CasualtiesResult> getListXY(CasualtiesDECalculationParam param) {
        StringBuilder sql = new StringBuilder("select * from " + TABLE_CALCULATION);
        StringBuilder whereSql = new StringBuilder(" where 1 = 1 and del_flag='0'");
        //评估记录id
        if (!PlatformObjectUtils.isEmpty(param.getId())) {
            whereSql.append(" and assess_id = '" + param.getId() + "'");
        }
        if ("0".equals(param.getSeismicInputMode())) {
            //表示烈度 ld1-ld5
            if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
                if ("ld1".equals(param.getCondition())) {
                    whereSql.append(" and intensity = 'Ⅵ' ");
                } else if ("ld2".equals(param.getCondition())) {
                    whereSql.append(" and intensity = 'Ⅶ' ");
                } else if ("ld3".equals(param.getCondition())) {
                    whereSql.append(" and intensity = 'Ⅷ' ");
                } else if ("ld4".equals(param.getCondition())) {
                    whereSql.append(" and intensity = 'Ⅸ' ");
                } else if ("ld5".equals(param.getCondition())) {
                    whereSql.append(" and intensity = 'Ⅹ' ");
                }
            }
        } else if ("1".equals(param.getSeismicInputMode())) {
            //四个概率水准  condition就是四个概率水准版本
            if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
                whereSql.append(" and condition='").append(param.getCondition()).append("'");
            }
        } else if ("2".equals(param.getSeismicInputMode())) {
            //表示影响场Condition时版本
            if (!PlatformObjectUtils.isEmpty(param.getCondition())) {
                whereSql.append(" and version = '").append(param.getCondition()).append("' ");
            }
        }
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(CasualtiesResult.class));
    }

    @Override
    public void batchSaveCasualtyResult(List<CasualtiesResult> finalResultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(finalResultList.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,create_user,create_time, geom,del_flag,day_death,day_injure,night_death,night_injure,condition,version,intensity,geography_id," +
                "day_death_correction_value,day_injure_correction_value,night_death_correction_value,night_injure_correction_value,flag)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:createUser,:createTime, ST_GeometryFromText(:geom,4490),:delFlag,:dayDeath,:dayInjure,:nightDeath,:nightInjure,:condition,:version,:intensity,:geographyId," +
                ":dayDeathCorrectionValue,:dayInjureCorrectionValue,:nightDeathCorrectionValue,:nightInjureCorrectionValue,:flag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<EarthquakeAffectedField> getInfluenceListByVersion(String influenceVersion) {
        String sql = "select fsi_id,area,intensity,geom from xzch_fast_seismic_influence where del_flag='0' and fv_id=(select fv_id from xzch_fast_si_version where version_code = ? and del_flag='0')";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EarthquakeAffectedField.class), influenceVersion);
    }

    @Override
    public void batchSaveCasualtyTempResult(List<CasualtiesResult> finalResultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(finalResultList.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION_TEMP + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,create_user,create_time, geom,del_flag,day_death,day_injure,night_death,night_injure,condition,intensity,version,geography_id," +
                "day_death_correction_value,day_injure_correction_value,night_death_correction_value,night_injure_correction_value,flag)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:createUser,:createTime, ST_GeometryFromText(:geom,4490),:delFlag,:dayDeath,:dayInjure,:nightDeath,:nightInjure,:condition,:intensity,:version,:geographyId," +
                ":dayDeathCorrectionValue,:dayInjureCorrectionValue,:nightDeathCorrectionValue,:nightInjureCorrectionValue,:flag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<CasualtiesResultVO> getTempEvaluateResult(List<String> idList, String assessId) {
        String sql = "SELECT " +
                "A.*," +
                "st_area ( st_intersection (  A.geom, b.geom ) ) AS area," +
                "b.id fsiId " +
                " FROM " +
                " fxqh_casualties_calculation_ed_temp " +
                " A LEFT JOIN ( SELECT intensity_ch, geom, earthquake_level, id FROM fxqh_setting_seismic_influenceflied WHERE id in (:idList) AND del_flag = '0' ) b ON A.condition = b.id " +
                " WHERE" +
                " A.intensity = b.intensity_ch " +
                "  AND A.assess_id = '" + assessId + "'" +
                " AND st_intersects (  A.geom, b.geom )";
        Map idMap = new HashMap();
        idMap.put("idList", idList);
        return namedParameterJdbcTemplate.query(sql.toString(), idMap, new BeanPropertyRowMapper<>(CasualtiesResultVO.class));
    }

    @Override
    public List<String> getSeismicInfluence(String intensityVersion) {
        String sql = "select fv_id from xzch_fast_si_version where del_flag='0' and version_code='" + intensityVersion + "'";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public void batchSaveCasualtyResultVO(List<CasualtiesResultVO> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "INSERT INTO " + TABLE_CALCULATION + " (id,assess_id,assess_code,province_name,city_name," +
                "county_name,create_user,create_time, geom,del_flag,day_death,day_injure,night_death,night_injure,condition,version,intensity,geography_id," +
                "day_death_correction_value,day_injure_correction_value,night_death_correction_value,night_injure_correction_value,flag)" +
                "values (:id,:assessId,:assessCode,:provinceName,:cityName," +
                ":countyName,:createUser,:createTime, ST_GeometryFromText(st_astext(:geom),4490),:delFlag,:dayDeath,:dayInjure,:nightDeath,:nightInjure,:condition,:version,:intensity,:geographyId," +
                ":dayDeathCorrectionValue,:dayInjureCorrectionValue,:nightDeathCorrectionValue,:nightInjureCorrectionValue,:flag)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deleteEvaluateResult(String assessId, String seismicInputMode) {
        jdbcTemplate.update("delete from fxqh_casualties_calculation_ed where assess_id='" + assessId +
                "' and flag='"+seismicInputMode+"'");
    }
    @Override
    public void deleteEvaluateResult1(String assessId) {
        jdbcTemplate.update("delete from fxqh_casualties_calculation_ed where assess_id='");
    }


    @Override
    public void batchDelete(String assessId) {
        jdbcTemplate.update("delete from fxqh_casualties_calculation_ed_temp where assess_id='" + assessId + "'");
    }

    @Override
    public List<ProbabilityLevel> getAllPgaByVersion(String version, String province, String city, String county) {
        String sql = "select id,CAST(pga50year63 as VARCHAR) pga50year63 ,CAST(pga50year10 as VARCHAR) pga50year10,CAST(pga50year2 as VARCHAR) pga50year2,CAST(pga100year1 as VARCHAR) pga100year1,province,city,county,geom,extends1 from jc_pga where del_flag='0' where data_version='" + version + "' and " +
                " province='" + province + "' and city='" + city + "' and county='" + county + "'";
        return jdbcTemplate.queryForList(sql, ProbabilityLevel.class);
    }

    @Override
    public List<ProbabilityLevel> getAllIntensityByVersion(String version, String province, String city, String county) {
        String sql = "select id,pga50year63,pga50year10,pga50year2,pga100year1,province,city,county,geom,extends1 from jc_ld where del_flag='0' where data_version='" + version + "' and " +
                " province='" + province + "' and city='" + city + "' and county='" + county + "'";
        return jdbcTemplate.queryForList(sql, ProbabilityLevel.class);
    }

    @Override
    public List<StructureHouseUsingPercent> getStructureHouseUsingPercent() {
        String sql = "select * from house_using_percent";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(StructureHouseUsingPercent.class));
    }

    @Override
    public List<ProbabilityLevel> getLdList(String assessId, String ppName, String cityName, String name, String version) {
        String sql = "select * from jc_ld where  del_flag='0' and province='" + ppName + "' and city='" + cityName + "' and county='" + name + "' and data_version='" + version + "' and assess_id='" + assessId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ProbabilityLevel.class));
    }

    @Override
    public List<ProbabilityLevel> getPagList(String assessId, String ppName, String cityName, String name, String version) {
        String sql = "select * from jc_pga where  del_flag='0' and province='" + ppName + "' and city='" + cityName + "' and county='" + name + "' and data_version='" + version + "' and assess_id='" + assessId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ProbabilityLevel.class));
    }

    @Override
    public List<Map<String, Object>> getSumByDivisionAndAssessCodes(String province, String city, String county,
                                                                    String assessCodes, String condition, String seismicInputMode) {
        String sql = "";
        if("2".equals(seismicInputMode)){
            sql = "select sum(cast(t1.night_death as float)) sum,count(t1.assess_id) count from fxqh_casualties_calculation_ed t1" +
                    " left join (SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='"+assessCodes+"') t2 on t1.assess_id=t2.id" +
                    " where t1.province_name='" + province + "' and t1.city_name='" + city + "' and t1.county_name='" + county + "'" +
                    " and t2.del_flag='0'" ;
            if("0".equals(seismicInputMode)){
                //烈度
                sql+=" and t1.intensity='"+condition+"' ";
            }else{
                //4个概率水准或影响场
                sql+=" and t1.condition='"+condition+"' ";
            }
            sql+=" group by t1.extends1 order by count(t1.assess_id) desc";
        }else{
            sql = "select sum(cast(t1.night_death as float)) sum,count(t1.assess_id) count from fxqh_casualties_calculation_ed t1" +
                    " left join (SELECT * FROM fxqh_calculation_tasks WHERE del_flag = '0' AND id='"+assessCodes+"') t2 on t1.assess_id=t2.id" +
                    " where t1.province_name='" + province + "' and t1.city_name='" + city + "' and t1.county_name='" + county + "'" +
                    " and t2.del_flag='0'" ;
                    if("0".equals(seismicInputMode)){
                        //烈度
                        sql+=" and t1.intensity='"+condition+"' ";
                    }else{
                        //4个概率水准或影响场
                        sql+=" and t1.condition='"+condition+"' ";
                    }
                    sql+=" group by t1.extends1 order by count(t1.assess_id) desc";
        }

        return jdbcTemplate.queryForList(sql);
    }
    //校验空间数据是否自相交
    @Override
    public String isGeomValid(String geomText) {
        String sql = "SELECT ST_IsValid(geom) FROM st_geomfromtext(?,4490) geom";

        return jdbcTemplate.queryForObject(sql, String.class, geomText);
    }

    @Override
    public List<Calculation> getValue(List<String> taskIdList, String code, String type) {
        String sql = "";
        if("0".equals(code)){
            if("1".equals(type)){
                sql = "select sum(day_death_correction_value) val,province_name areaName ,case when intensity='Ⅵ' then '1' " +
                        " when intensity='Ⅶ' then '2'  when intensity='Ⅷ' then '3' " +
                        " when intensity='Ⅸ' then '4'  when intensity='Ⅹ' then '5' else 0 end intensity from fxqh_casualties_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') and intensity in ('Ⅵ', 'Ⅶ','Ⅸ','Ⅷ','Ⅹ') GROUP BY intensity,province_name order by val";
            }else{
                //sql = "select sum(day_injure_correction_value) val,intensity from fxqh_casualties_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') and intensity in ('Ⅵ', 'Ⅶ','Ⅸ','Ⅷ','Ⅹ') GROUP BY intensity order by val";
                sql = "select sum(day_injure_correction_value) val,province_name areaName ,case when intensity='Ⅵ' then '1' " +
                        " when intensity='Ⅶ' then '2'  when intensity='Ⅷ' then '3' " +
                        " when intensity='Ⅸ' then '4'  when intensity='Ⅹ' then '5' else 0 end intensity from fxqh_casualties_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') and intensity in ('Ⅵ', 'Ⅶ','Ⅸ','Ⅷ','Ⅹ') GROUP BY intensity,province_name order by val";
            }
        }else{
            if("1".equals(type)){
                sql = "select sum(day_death_correction_value) val,city_name as area_name from fxqh_casualties_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') GROUP BY area_name order by val";
            }else{
                sql = "select sum(day_injure_correction_value) val,city_name as area_name from fxqh_casualties_calculation_ed where assess_id in ('"+String.join("','", taskIdList)+"') GROUP BY area_name order by val";
            }
        }
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Calculation.class));
    }

    @Override
    public List<CasualtiesCalculationVO> getCasualtiesResult(String taskId, String province, String condition, String casualties, String time, String seismicInputMode) {
        StringBuilder sqlBuilder = new StringBuilder("select province_name,city_name,county_name ");
        //重伤
        if ("rysw1".equals(casualties)) {
            sqlBuilder.append(",").append("'重伤' as casualties");
            if ("sjd1".equals(time)) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_injure as calculatedValue");
                sqlBuilder.append(",").append(" day_injure_correction_value as correctionValue");
            } else if ("sjd2".equals(time)) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_injure as calculatedValue");
                sqlBuilder.append(",").append(" night_injure_correction_value as correctionValue");
            }
        } else if ("rysw2".equals(casualties)) {
            //死亡
            sqlBuilder.append(",").append("'死亡' as casualties");
            if ("sjd1".equals(time)) {
                //白天
                sqlBuilder.append(",").append("'白天' as time");
                sqlBuilder.append(",").append(" day_death as calculatedValue");
                sqlBuilder.append(",").append(" day_death_correction_value as correctionValue");
            } else if ("sjd2".equals(time)) {
                //夜间
                sqlBuilder.append(",").append("'夜间' as time");
                sqlBuilder.append(",").append(" night_death as calculatedValue");
                sqlBuilder.append(",").append(" night_death_correction_value as correctionValue");
            }
        }
        StringBuilder whereSql = new StringBuilder(" from fxqh_casualties_calculation_ed where del_flag='0' ");
        if (StringUtils.isNotBlank(taskId)) {
            whereSql.append(" and assess_id = '").append(taskId).append("' and province_name='").append(province).append("'");
        }
        if ("0".equals(seismicInputMode)) {
            //烈度 condition就是烈度 ld1-ld5  casualties
            if (StringUtils.isNotBlank(condition)) {
                if ("ld1".equals(condition)) {
                    whereSql.append(" and intensity='Ⅵ'");
                } else if ("ld2".equals(condition)) {
                    whereSql.append(" and intensity='Ⅶ'");
                } else if ("ld3".equals(condition)) {
                    whereSql.append(" and intensity='Ⅷ'");
                } else if ("ld4".equals(condition)) {
                    whereSql.append(" and intensity='Ⅸ'");
                } else if ("ld5".equals(condition)) {
                    whereSql.append(" and intensity='Ⅹ'");
                }
            }
        } else if ("1".equals(seismicInputMode)) {
            //四个概率水准  condition就是四个概率水准版本
            whereSql.append(" and condition='").append(condition).append("'");
        } else if ("2".equals(seismicInputMode)) {
            if (StringUtils.isNotBlank(condition)) {
                //地震影响场 condition就是地震影响场版本
                whereSql.append(" and version='").append(condition).append("'");
            }
        }
        whereSql.append(" and flag='").append(seismicInputMode).append("'");
        sqlBuilder.append(whereSql);
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(CasualtiesCalculationVO.class));
    }
}
